<?php if(!defined('BASEPATH')) exit('No direct script allowed');



class mdl_piutang_pajak_per_wilayah extends CI_Model{

	

	function __construct()
	{
		parent::__construct();
	}
	
	// t_trnkb -> data sedikit, ada kd_status
	// t_mstkb -> data banyak, tidak ada kd_status
	function getItem($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 
	
		if (!$periode){$periode = 0;}
		if (!$kode_wil){$kode_wil = 0;}
		if (!$jenis_kb){$jenis_kb = 0;}
		$query = "select t_mstkb.kd_wilayah , t_mstkb.kd_jenis_kb, 
					(select t_wilayah.nm_wilayah from t_wilayah where t_wilayah.kd_wilayah = t_mstkb.kd_wilayah) as nm_wilayah,
					(select t_jeniskb.uraian from t_jeniskb where t_jeniskb.kode  = t_mstkb.kd_jenis_kb) as uraian, 
					sum(bea_pkb_pok) as pkb 
					from t_mstkb  
					where 
					bea_pkb_pok > 0 
					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '12-31-$periode'    
					";
		if (($kode_wil !='all'))
			{$query .= "and kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and  kd_jenis_kb='$jenis_kb'";}	
			$query	.="group by kd_wilayah,kd_jenis_kb ";
			
			$query	.="order by kd_wilayah , kd_jenis_kb";
		$this->db->flush_cache();
		//echo $query;echo'------';
		
		return $this->db->query($query);
	}
	
	function getItemCount($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 
		if (!$periode){$periode = 0;}
		if (!$kode_wil){$kode_wil = 0;}
		if (!$jenis_kb){$jenis_kb = 0;}
		$query = "select kd_wilayah , kd_jenis_kb,
					sum(bea_pkb_pok) as sudah_bayar 
					from t_mstkb 
					where 
					bea_pkb_pok > 0

					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '12-31-$periode'  
					 
					and tg_bayar >= '01-01-$periode' 
					and tg_bayar <= '12-31-$periode'   
					";
		if (($kode_wil !='all'))
			{$query .= "and kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and  kd_jenis_kb='$jenis_kb'";}	
			
			$query	.="group by kd_wilayah,kd_jenis_kb ";
			
			$query	.="order by kd_wilayah , kd_jenis_kb";
		$this->db->flush_cache();
		//echo $query;echo'------';
		$result = $this->db->get($query);
		//echo $result->num_rows();		
		// return $result->num_rows();		
		//return $this->db->count_all_results($query);
		return $this->db->query($query);
	}
	
	function getItemGrid($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 
		if (!$periode){$periode = 0;}
		if (!$kode_wil){$kode_wil = 0;}
		if (!$jenis_kb){$jenis_kb = 0;}
		
		$page = isset($_POST['page']) ? intval($_POST['page']) : 1;  
		$limit = isset($_POST['rows']) ? intval($_POST['rows']) : 10;  
		$count = $this->getItemGridCount($periode,$kode_wil,$jenis_kb);
		
		$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'kd_wilayah';  
		$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		
		$response->total = $count;
		$offset = ($page-1)*$limit;
		
		$sum_piutang = 0; 
		$sum_sudah_bayar = 0; 
		$sum_sisa = 0; 
		if ($count>0){
			/*$query = "select t_mstkb.kd_wilayah , t_mstkb.kd_jenis_kb, 
					(select t_wilayah.nm_wilayah from t_wilayah where t_wilayah.kd_wilayah = t_mstkb.kd_wilayah) as nm_wilayah,
					(select t_jeniskb.uraian from t_jeniskb where t_jeniskb.kode  = t_mstkb.kd_jenis_kb) as uraian, 
					sum(bea_pkb_pok) as pkb 
					from t_mstkb  
					where 
					bea_pkb_pok > 0 
					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '12-31-$periode'    
					";
			if (($kode_wil !='all'))
				{$query .= "and kd_wilayah='$kode_wil'";}
			if (($jenis_kb !='all'))
				{$query .= "and  kd_jenis_kb='$jenis_kb'";}	
				
			$query	.="group by kd_wilayah,kd_jenis_kb ";
			$query	.="order by kd_wilayah , kd_jenis_kb";*/
			
			if (($kode_wil !='all'))
				$this->db->where('kd_wilayah', $kode_wil);	
			if (($jenis_kb !='all'))
				$this->db->where('kd_jenis_kb', $jenis_kb);
			
			$this->db->order_by($sort." ".$order);
			$this->db->group_by(array("kd_wilayah", "kd_jenis_kb")); 
			
			$this->db->select("
						a.kd_wilayah,
						a.kd_jenis_kb,
						(select b.nm_wilayah from t_wilayah b where b.kd_wilayah = a.kd_wilayah) as nm_wilayah,
						(select c.uraian from t_jeniskb c where c.kode  = a.kd_jenis_kb) as uraian,
						sum(bea_pkb_pok) as pkb",
						false);
			$this->db->from('t_mstkb a');
			$this->db->where('bea_pkb_pok >', 0);
			$this->db->where("tg_tetap >=", '01-01-'.$periode);
			$this->db->where("tg_tetap <=", '12-31-'.$periode);
			
			//echo $query;echo'------';
			$query = $this->db->get();
			
			$i=0;
			foreach ($query->result() as $row)
			{
				$piutang = $this->piutang($periode, $kode_wil, $jenis_kb);
				$sudah_bayar = $this->piutang_pajak_per_wilayah->sudah_bayar($periode, $kode_wil, $jenis_kb);
				$row2 = $piutang->result_array(); 
				$row3 = $sudah_bayar->result_array();
				
				$response->rows[$i]['num']=$i+1;	
				$response->rows[$i]['wilayah']=$row->kd_wilayah." - ".$row->nm_wilayah;	
				$response->rows[$i]['jenis']=$row->kd_jenis_kb." - ".$row->uraian;	
				$response->rows[$i]['piutang']=convert_rupiah_non_rp($row2[$i]['piutang']);	
				$response->rows[$i]['sudah_bayar']=convert_rupiah_non_rp($row3[$i]['sudah_bayar']);
				$response->rows[$i]['sisa_bayar']=convert_rupiah_non_rp(($row2[$i]['piutang']-$row3[$i]['sudah_bayar']));
				//$response->rows[$i]['pkb']=$row->pkb;	
				
				$sum_piutang = $sum_piutang + $row2[$i]['piutang']; 
				$sum_sudah_bayar = $sum_sudah_bayar + $row3[$i]['sudah_bayar']; 
				$sum_sisa = $sum_sisa + ($row2[$i]['piutang']-$row3[$i]['sudah_bayar']); 
			
				$i++;
			}
			
			$query->free_result();
		} else{
			$response->rows[0]['kd_wilayah']='';	
			$response->rows[0]['kd_jenis_kb']='';	
			$response->rows[0]['nm_wilayah']='';	
			$response->rows[0]['uraian']='';	
			// $response->rows[0]['pkb']='';	
		}
		
		$response->footer[0]['wilayah']="JUMLAH";
		$response->footer[0]['piutang']=convert_rupiah_non_rp($sum_piutang);
		$response->footer[0]['sudah_bayar']=convert_rupiah_non_rp($sum_sudah_bayar);
		$response->footer[0]['sisa_bayar']=convert_rupiah_non_rp($sum_sisa);
		
		return json_encode($response);
	}
	
	function getItemGridCount($periode=0, $kode_wil=null, $jenis_kb=null)
	{
		/*$query = "select t_mstkb.kd_wilayah , t_mstkb.kd_jenis_kb, 
				(select t_wilayah.nm_wilayah from t_wilayah where t_wilayah.kd_wilayah = t_mstkb.kd_wilayah) as nm_wilayah,
				(select t_jeniskb.uraian from t_jeniskb where t_jeniskb.kode  = t_mstkb.kd_jenis_kb) as uraian, 
				sum(bea_pkb_pok) as pkb 
				from t_mstkb  
				where 
				bea_pkb_pok > 0 
				and tg_tetap >= '01-01-$periode' 
				and tg_tetap <= '12-31-$periode'    
				";
		if (($kode_wil !='all'))
			{$query .= "and kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and  kd_jenis_kb='$jenis_kb'";}	
			
		$query	.="group by kd_wilayah,kd_jenis_kb ";
		$query	.="order by kd_wilayah , kd_jenis_kb";*/
		
		if (($kode_wil !='all'))
			$this->db->where('kd_wilayah', $kode_wil);	
		if (($jenis_kb !='all'))
			$this->db->where('kd_jenis_kb', $jenis_kb);
			
		$this->db->group_by(array("kd_wilayah", "kd_jenis_kb")); 
		
		$this->db->select("a.kd_wilayah,
					a.kd_jenis_kb,
					(select b.nm_wilayah from t_wilayah b where b.kd_wilayah = a.kd_wilayah) as nm_wilayah,
					(select c.uraian from t_jeniskb c where c.kode  = a.kd_jenis_kb) as uraian,
					sum(bea_pkb_pok) as pkb",
					false);
		$this->db->from('t_mstkb a');
		$this->db->where('bea_pkb_pok >', 0);
		$this->db->where("tg_tetap >=", '01-01-'.$periode);
		$this->db->where("tg_tetap <=", '12-31-'.$periode);
			
		//echo $query;echo'------';
		$result = $this->db->get();
		//echo $result->num_rows();	
		$i=0;
		foreach ($result->result() as $row)
		{
			$i++;
		}

		return $i;		
	}
	 
	function piutang($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 	
		if (!$periode){$periode = 0;}
		if (!$kode_wil){$kode_wil = 0;}
		if (!$jenis_kb){$jenis_kb = 0;}
		$query = "select   kd_wilayah , kd_jenis_kb,
					sum(bea_pkb_pok) as piutang 
					from t_mstkb 
					where 
					bea_pkb_pok > 0

					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '12-31-$periode'   
  
					";
		 if (($kode_wil !='all'))
			{$query .= "and  kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and  kd_jenis_kb='$jenis_kb'";}	 
			$query	.="group by  kd_wilayah,kd_jenis_kb "; 
			$query	.="order by kd_wilayah , kd_jenis_kb";
		$this->db->flush_cache();
		//echo $query;echo'------';
		return $this->db->query($query);
	}


	function sudah_bayar($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 
		if (!$periode){$periode = 0;}
		if (!$kode_wil){$kode_wil = 0;}
		if (!$jenis_kb){$jenis_kb = 0;}
		$query = "select   kd_wilayah , kd_jenis_kb,
					sum(bea_pkb_pok) as sudah_bayar 
					from t_mstkb 
					where 
					bea_pkb_pok > 0

					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '12-31-$periode'  
					 
					and tg_bayar >= '01-01-$periode' 
					and tg_bayar <= '12-31-$periode'   
					";
		 
		if (($kode_wil !='all'))
			{$query .= "and  kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and  kd_jenis_kb='$jenis_kb'";}	
			$query	.="group by  kd_wilayah,kd_jenis_kb ";
			$query	.="order by kd_wilayah , kd_jenis_kb";
			 
		$this->db->flush_cache();
		//echo $query;echo'------';
		return $this->db->query($query);
	}

/*
and tg_tetap >= '$periode-01-01' 
and tg_tetap <= '$periode-12-31'  
 
and tg_bayar >= '$periode-01-01' 
and tg_bayar <= '$periode-12-31'  
*/
	
	function getKodeWil()
	{
		$query = "select * from t_wilayah order by  kd_wilayah asc";
		$this->db->flush_cache();
		return $this->db->query($query);
	}
	
	
	function getJenisKB()
	{
		$query = "select * from t_jeniskb order by kode";
		$this->db->flush_cache();
		return $this->db->query($query);
	}
	
	function getKodeWilValue($id)
	{
		$query = "select nm_wilayah from t_wilayah where kd_wilayah = '$id'";
		$this->db->flush_cache();
		$result = $this->db->query($query);
		return $result->result_array();	
	}
	
	function getJenisKBValue($id)
	{
		$query = "select uraian from t_jeniskb where kode = '$id'";
		$this->db->flush_cache();
		$result = $this->db->query($query);
		return $result->result_array();	
	}
	
	function getItem_kas_awal($id)
	{
		$query = "SELECT 
					Sum(t_mstkb.bea_pkb_pok) AS pkb
					FROM
					t_mstkb
					left JOIN t_wilayah ON t_wilayah.kd_wilayah = t_mstkb.kd_wilayah
					left JOIN t_jeniskb ON t_mstkb.kd_jenis_kb = t_jeniskb.kode
					where tg_akhir_pkb <= '$id-12-31' and YEAR(tg_akhir_pkb)='$id'";
		$this->db->flush_cache();
		$result = $this->db->query($query);
		return $result->result_array();	
	}

}